package com.szholly.plug.form.excel.core.imp;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.utils.util.DateUtils;
import com.szholly.plug.form.excel.model.ColumnProperty;
import com.szholly.plug.form.excel.model.DictionaryEntity;
import com.szholly.plug.form.excel.model.TableEntity;
import com.szholly.plug.form.excel.utils.StringUtil;

public class ImpAfter extends ImpDataPar implements IImpAfter{

	public ImpAfter(TableEntity tableEntity,String userId,String dsName) {
		super(tableEntity,userId,dsName);
	}

	
	@SuppressWarnings("null")
	public List<Map<String, String>> getImpData(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String, String>> impData = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			StringBuilder sbColName = new StringBuilder();
			StringBuilder sbJoinDicTable = new StringBuilder();
			StringBuilder sbJoinDicTableCond = new StringBuilder();
			List<ColumnProperty>  listColumn = tableEntity.getColumnPropertys();
			ColumnProperty colProperty = null;
			DictionaryEntity dicEntity = null;
			for (int i = 0; i < colLength; i++) {
				colProperty = listColumn.get(i);
				if (i < colLength - 1) {
					if(colProperty.getDictEntity() != null){
						dicEntity = colProperty.getDictEntity();
						String strTableName = dicEntity.getName();
						String strDicId = dicEntity.getDictId();
						String strDicName = dicEntity.getDictName();
						String strDicType = dicEntity.getDictType();
						String strDicTypeValue = dicEntity.getDictTypeValue();
						sbColName.append(colProperty.getColumnName()).append(",");
						sbColName.append("a" + i + ".").append(strDicId).append(" ").append(colProperty.getColumnName()).append("_DicId").append(",");
						sbJoinDicTable.append(" LEFT JOIN ").append(strTableName).append(" a" + i)
						.append(" ON ").append(" a" + i + ".").append(strDicName).append(" = ").append("a.").append(colProperty.getColumnName());
						if(StringUtil.IsNotNullOrEmpty(strDicType) && StringUtil.IsNotNullOrEmpty(strDicTypeValue)){
							sbJoinDicTable.append(" AND ").append(" a" + i + ".").append(strDicType).append(" = ").append("'").append(strDicTypeValue).append("'");
						}
					}else{
						sbColName.append(" a." + colProperty.getColumnName() + ",");
					}
				}
				if (i == colLength - 1) {
					if(colProperty.getDictEntity() != null){
						dicEntity = colProperty.getDictEntity();
						String strTableName = dicEntity.getName();
						String strDicId = dicEntity.getDictId();
						String strDicName = dicEntity.getDictName();
						String strDicType = dicEntity.getDictType();
						String strDicTypeValue = dicEntity.getDictTypeValue();
						sbColName.append(colProperty.getColumnName()).append(",");
						sbColName.append("a" + i + ".").append(strDicId).append(" ").append(colProperty.getColumnName()).append("_DicId");
						sbJoinDicTable.append(" LEFT JOIN ").append(strTableName).append(" a" + i)
						.append(" ON ").append(" a" + i + ".").append(strDicName).append(" = ").append("a.").append(colProperty.getColumnName());
						if(StringUtil.IsNotNullOrEmpty(strDicType) && StringUtil.IsNotNullOrEmpty(strDicTypeValue)){
							sbJoinDicTable.append(" AND ").append(" a" + i + ".").append(strDicType).append(" = ").append("'").append(strDicTypeValue).append("'");
						}
					}
					else{
						sbColName.append(" a." + colProperty.getColumnName());
					}
				}
			}
			StringBuilder sbSqlSelData = new StringBuilder(" select ")
			.append(sbColName)
			.append(" from ").append(tableName).append(TEMP_TABEL_SUFFIX).append(userId).append(" a ")
			.append(sbJoinDicTable.toString())
			.append(" WHERE 1=1 ")
			.append(sbJoinDicTableCond.toString());
			System.out.println(sbSqlSelData.toString());
			stmt =  conn.createStatement();
			rs = stmt.executeQuery(sbSqlSelData.toString());
			impData = new ArrayList<Map<String,String>>();
			Map<String,String> mapData = null;
			while(rs.next()){
				mapData = new HashMap<String, String>();
				for (String colName: arrColumn) {
					if(tableEntity.getDictColumnName().contains(colName)){
						mapData.put(colName + "_DicId", rs.getString(colName + "_DicId"));
					}
					mapData.put(colName, rs.getString(colName));
				}
				impData.add(mapData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(stmt);
		}
		return impData;
	}

	
	public List<Map<String, String>> getErrorData(List<Map<String, String>> selTempData) {
		List<Map<String, String>> errorData = new ArrayList<Map<String,String>>();
		boolean bErrFlag = false;
		StringBuilder sbErrorInfo = null;
		Map<String,String> mapColumnName = tableEntity.getMapColumnName();
		for(Map<String,String> rowData: selTempData){
			sbErrorInfo = new StringBuilder();
			bErrFlag = false;
			for(ColumnProperty colProperty: tableEntity.getColumnPropertys()){
				String strColumnName = colProperty.getColumnName();
				String strCHColName = mapColumnName.get(strColumnName);
				String strRequired = colProperty.getRequired();
				String strDateType = colProperty.getDataType();
				String strValue  = rowData.get(strColumnName);
				String strDefValue = colProperty.getDefaultValue();
				
//				判断字典项关联错误
				if(tableEntity.getDictColumnName().contains(strColumnName)){
					if(StringUtil.IsNotNullOrEmpty(strValue)){
						if(StringUtil.IsNullOrEmpty(rowData.get(strColumnName + "_DicId"))){
							if(!bErrFlag){
								bErrFlag = true;
							}
							sbErrorInfo.append(strCHColName + ERROR_DICT_INFO);
						}
					}
				}
//				判断唯一值
				if(strColumnName.equals(tableEntity.getUniqueColumn())){
					if(!isUnique(strColumnName, strValue)){
						if(!bErrFlag){
							bErrFlag = true;
						}
						sbErrorInfo.append(strCHColName + ERROR_REPEAT_INFO);
					}
				}
//				判断数据必填
				if(strRequired.equals("true")){
					if(StringUtil.IsNullOrEmpty(strValue)){
						if(!bErrFlag){
							bErrFlag = true;
						}
						sbErrorInfo.append(strCHColName + ERROR_REQUIRED_INFO);
					}
				}
//				判断数据类型
				if(!tableEntity.getDictColumnName().contains(strColumnName)){
					if(StringUtil.IsNotNullOrEmpty(strValue) || StringUtil.IsNotNullOrEmpty(strDefValue)){
						if(!judgeDataType(strDateType, strValue, strDefValue)){
							if(!bErrFlag){
								bErrFlag = true;
							}
							sbErrorInfo.append(strCHColName + ERROR_DATATYPE_INFO);
						}
					}
				}
			}
			if(bErrFlag){
				rowData.put("errorinfo", sbErrorInfo.toString());
				errorData.add(rowData);
			}
			else{
				insertMapData(rowData);
			}
		}
		return errorData;
	}
	
	
	@SuppressWarnings("null")
	public void insertData(List<Map<String, String>> impData) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			
			List<ColumnProperty> listCol = tableEntity.getColumnPropertys();
			StringBuilder sbSqlInsertData = new StringBuilder(" insert into ").append(tableName)
			.append("(");
			sbSqlInsertData.append(tableEntity.getPrimaryKey()).append(",");
			sbSqlInsertData.append(getStrColName());
			sbSqlInsertData.append(") VALUES (");
			sbSqlInsertData.append("?,");
			sbSqlInsertData.append(getStrParam());
			pstmt = conn.prepareStatement(sbSqlInsertData.toString());
			ColumnProperty colProperty = null;
			Long maxId = getMaxId();
			for (Map<String, String> mapData : impData) {
				pstmt.setLong(1, --maxId);
				for (int m = 0; m < colLength; m++) {
					int idx = m + 2;
					colProperty = listCol.get(m);
					String strDataType = colProperty.getDataType();
					if(tableEntity.getDictColumnName().contains(arrColumn.get(m))){
						if(STRING_TYPE.equals(strDataType)){
							pstmt.setString(idx, mapData.get(arrColumn.get(m) + "_DicId"));
						}
						if(INTEGER_TYPE.equals(strDataType)){
							pstmt.setInt(idx, Integer.parseInt(mapData.get(arrColumn.get(m) + "_DicId")));
						}
						if(DOUBLE_TYPE.equals(strDataType)){
							pstmt.setDouble(idx, Double.parseDouble(mapData.get(arrColumn.get(m) + "_DicId")));
						}
						if(DATE_TYPE.equals(strDataType)){
							pstmt.setDouble(idx, Double.parseDouble(mapData.get(arrColumn.get(m) + "_DicId")));
						}
					}
					else{
						if(STRING_TYPE.equals(strDataType)){
							pstmt.setString(idx, mapData.get(arrColumn.get(m)));
						}
						if(INTEGER_TYPE.equals(strDataType)){
							pstmt.setInt(idx, Integer.parseInt(mapData.get(arrColumn.get(m))));
						}
						if(DOUBLE_TYPE.equals(strDataType)){
							pstmt.setDouble(idx, Double.parseDouble(mapData.get(arrColumn.get(m))));
						}
						if(DATE_TYPE.equals(strDataType)){
							pstmt.setDouble(idx, Double.parseDouble(mapData.get(arrColumn.get(m))));
						}
					}
				}
				pstmt.addBatch();
			}
			pstmt.executeBatch();;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(pstmt);
		}
	}
	
	
	@SuppressWarnings("null")
	public void dropTempTable(){
		Connection conn = null;
		Statement stmt = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			stmt = conn.createStatement();
			StringBuilder sbSqlDropTable = new StringBuilder(
					" drop table ");
			sbSqlDropTable.append(tableName).append(TEMP_TABEL_SUFFIX).append(userId);//临时表加后缀
			stmt.execute(sbSqlDropTable.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(stmt);
		}
	}
	
	@SuppressWarnings("null")
	private void insertMapData(Map<String, String> impMapData) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			
			List<ColumnProperty> listCol = tableEntity.getColumnPropertys();
			StringBuilder sbSqlInsertData = new StringBuilder(" insert into ").append(tableName)
			.append("(");
			sbSqlInsertData.append(tableEntity.getPrimaryKey()).append(",");
			sbSqlInsertData.append(getStrColName());
			sbSqlInsertData.append(") VALUES (");
			sbSqlInsertData.append("?,");
			sbSqlInsertData.append(getStrParam());
			pstmt = conn.prepareStatement(sbSqlInsertData.toString());
			ColumnProperty colProperty = null;
			Long maxId = getMaxId();
			pstmt.setLong(1, --maxId);
			for (int m = 0; m < colLength; m++) {
				int idx = m + 2;
				colProperty = listCol.get(m);
				String strDataType = colProperty.getDataType();
				String strDefaultValue = colProperty.getDefaultValue();
				String strColValue = "";
				if(strDefaultValue == null || "".equals(strDefaultValue)){
					if(tableEntity.getDictColumnName().contains(arrColumn.get(m))){
						strColValue = impMapData.get(arrColumn.get(m) + "_DicId");
					}
					else{
						strColValue = impMapData.get(arrColumn.get(m));
					}
				}
				else{
					strColValue = strDefaultValue;
				}
				if(StringUtil.IsNullOrEmpty(strColValue)){
					pstmt.setNull(idx, Types.NULL);
				}
				else{
					if(STRING_TYPE.equals(strDataType)){
						if(CURRENT_USER.equals(strDefaultValue)){
							pstmt.setString(idx, userId);
						}
						else{
							pstmt.setString(idx, strColValue);
						}
					}
					else if(INTEGER_TYPE.equals(strDataType)){
						if(CURRENT_USER.equals(strDefaultValue)){
							pstmt.setInt(idx, Integer.parseInt(userId));
						}
						else{
							pstmt.setInt(idx, Integer.parseInt(strColValue));
						}
					}
					else if(DOUBLE_TYPE.equals(strDataType)){
						pstmt.setDouble(idx, Double.parseDouble(strColValue));
					}
					else if(DATE_TYPE.equals(strDataType)){
						if(CURRENT_DATE.equals(strDefaultValue)){
							java.util.Date date = new java.util.Date();
							pstmt.setDate(idx,Date.valueOf(DateUtils.convertDateToString(date)));
						}
						else{
							java.util.Date date = DateUtils.convertStringToDate(strColValue);
							pstmt.setDate(idx, Date.valueOf(DateUtils.convertDateToString(date)));
						}
					}
					else if(DATETIME_TYPE.equals(strDataType)){
						if(CURRENT_DATE.equals(strDefaultValue)){
							java.util.Date date = new java.util.Date();
							java.sql.Timestamp d = new java.sql.Timestamp(date.getTime());
							pstmt.setTimestamp(idx,d);
						}
						else{
							java.util.Date date = DateUtils.convertStringToDateTime(strColValue);
							java.sql.Timestamp d = new java.sql.Timestamp(date.getTime());
							pstmt.setTimestamp(idx, d);
						}
					}
				}
			}
			pstmt.addBatch();
			pstmt.executeBatch();;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(pstmt);
		}
	}
	
	/**
	 * 获取数据库最大ID做为主键
	 * @return
	 */
	@SuppressWarnings("null")
	private long getMaxId(){
		long id = 0;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select min("+ tableEntity.getPrimaryKey() +") from " + tableName;
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				id = rs.getLong(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
//			DBUtil.close(stmt);
//			DBUtil.close(conn);
		}
		return id;
	}

	@SuppressWarnings("null")
	private boolean isUnique(String strColName,String strColVal){
		boolean bUnique = true;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select id from " + tableName + " where " + strColName + " = '" + strColVal + "'";
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.getRow() > 0) {
				bUnique = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
//			DBUtil.close(stmt);
//			DBUtil.close(conn);
		}
		return bUnique;
	}
	
	public List<Map<String, String>> getErrorData(String strErrorType) {
		return null;
	}
	
	private static boolean judgeDataType(String strDataType, String strColValue, String strDefValue){
		boolean flag = false;
		if(INTEGER_TYPE.equals(strDataType)){
			if(StringUtil.IsNullOrEmpty(strDefValue)){
				flag = isInteger(strColValue);
			}
			else{
				flag = isInteger(strDefValue);
			}
		}
		else if(STRING_TYPE.equals(strDataType)){
			flag = true;
		}
		else if(DOUBLE_TYPE.equals(strDataType)){
			if(StringUtil.IsNullOrEmpty(strDefValue)){
				flag = isDouble(strColValue);
			}
			else{
				flag = isDouble(strDefValue);
			}
		}
		else if(DATE_TYPE.equals(strDataType)){
			if(!CURRENT_DATE.equals(strDefValue)){
				if(StringUtil.IsNullOrEmpty(strDefValue)){
					flag = isDate(strColValue);
				}
				else{
					flag = isDate(strDefValue);
				}
			}
			else{
				flag = true;
			}
		}
		else if(DATETIME_TYPE.equals(strDataType)){
			if(!CURRENT_DATE.equals(strDefValue)){
				if(StringUtil.IsNullOrEmpty(strDefValue)){
					flag = isDateTime(strColValue);
				}
				else{
					flag = isDate(strDefValue);
				}
			}
			else{
				flag = true;
			}
		}
		return flag;
	}

	/**  * 判断字符串是否是整数  */ 
	public static boolean isInteger(String value) {
		try {
			Integer.parseInt(value);
			return true;
		} catch (NumberFormatException e) {
			return false;
		}
	}

	/** * 判断字符串是否是浮点数 */
	public static boolean isDouble(String value) {
		try {
			Double.parseDouble(value);
				return true;
		} catch (NumberFormatException e) {
			return false;
		}
	}
	
	/** * 判断字符串是否是日期类型 */
	public static boolean isDate(String value) {
		try {
			Date.valueOf(value);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean isDateTime(String value) {
		SimpleDateFormat sdf = null;
		ParsePosition pos = new ParsePosition(0);
		try {
			sdf = new SimpleDateFormat(DateUtils.DATA_YYY_MM_DD_HH_MM_SS);
			sdf.setLenient(false);
			java.util.Date date = sdf.parse(value);
			if(date == null){
				return false;
			}else{
				if(pos.getIndex() > sdf.format(date).length()){
					return false;
				}
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean checkDigit(double dNum, int digit) {
		String sNum = String.valueOf(dNum);
		for (int i = 0; i < sNum.length(); i++) {
			if (sNum.indexOf(".") != -1
					&& sNum.length() - (sNum.indexOf(".") + 1) > digit) {
				return false;
			}
		}
		return true;
	}
}